<?php

namespace app\admin\controller;

use app\admin\model\ClassModel;
use app\admin\model\SchoolModel;
use app\admin\model\StudentModel;
use cmf\controller\AdminBaseController;
use think\Controller;
use think\Db;
use think\db\Query;
use think\Request;

class StudentController extends AdminBaseController
{
    public function index()    {

        //获取当前请求的name变量
        $name=$this->request->param('name');
        //获取当前请求的school_id
        $school_id=$this->request->param('school_id');
        //获取当前请求的class_id
        $class_id=$this->request->param('class_id');
        //查询班级表中学校id
        $class=Db::name('class')->where('school_id',$school_id)->select();
        //查询学校表数据
        $school=Db::name('school')->select();
        //关联
        $student=Db::name('student')
            //连接班级表 班级id=学生表的class_id
            ->join('cmf_class','cmf_class.id=cmf_student.class_id')
            //连接学校表 学校id=班级表的school_id
            ->join('cmf_school','cmf_school.id=cmf_class.school_id')
            //重命名 班级名为class_name 学校名为school_name  查询学生表所有数据
            ->field('cmf_student.*,cmf_class.name as class_name,cmf_school.name as school_name')
            //接受所查询的学生的值
            ->where(function (Query $query) use ($name,$school_id,$class_id){
                //模糊查询
                if ($name){
                    $query->where('cmf_student.name','like',"%$name%");
                }
                //模糊查询
                if ($school_id){
                    $query->where('cmf_school.id','eq',$school_id);
                }
                //模糊查询
                if ($class_id){
                    $query->where('cmf_class.id','eq',$class_id);
                }
            })
            //倒序
            ->order('id DESC')
            //分页 保持页面稳定性 接受全部数据
            ->paginate('3',false,['query'=>$this->request->param()]);
        //分页样式
        $page=$student->render();
        //映射页面，接收返回的值
        return view('',['class'=>$class,'school'=>$school,'page'=>$page,'student'=>$student,
            'school_id'=>$school_id,'class_id'=>$class_id]);
    }

    public function add()
    {
        //查询学校表数据
        $school=Db::name('school')->select();
        //映射页面，接收返回的值
        return $this->fetch('',['school'=>$school]);
    }
    public function addPost()
    {
        //获取add页面传过来的值
        $data=input();
        //若姓名为空
        if (empty($data['name'])){
            //提示不能为空
            return ['code'=>100,'msg'=>'请输入姓名'];
        }
        //若idcard为空
        if (empty($data['idcard'])){
            //提示不能为空
            return ['code'=>100,'msg'=>'请输入身份证号'];
        }
        //若address为空
        if (empty($data['address'])){
            //提示不能为空
            return ['code'=>100,'msg'=>'请输入家庭地址'];
        }
        //销毁photo_name变量
        unset($data['photo_name']);
        //更新创建时间
        $data['create_time']=date('Y-m-d H:i:s',time());
        //在数据库中插入新的数据    （insert）
        $add=Db::name('student')->insert($data);
        //判断是否成功
        if ($add){
            //成功
            $this->success("添加成功！", url("student/index"));
        } else {
            //失败
            $this->error("添加失败！", url("student/add"));
        }
    }

    //根据学校id查询该学校下面的所有班级
    public function getAdd(){
        //接受学校id
        $schoolId=input('school_id');
        //halt($schoolId);
        //查询
        $class=Db::name('class')
            ->where('school_id',$schoolId)
            ->select();
        //halt($class);
        //处理
        if ($class){
            //第一个参数的数组（对应数组）
            $option=["<option value=''>请选择班级</option>"];
            //循环
            foreach ($class as $val){
                //$val['id'],$val['name']
                $opt="<option value='".$val['id']."'>".$val['name']."</option>";
                //array_push() 函数向第一个参数的数组尾部添加一个或多个元素（入栈），然后返回新数组的长度。
                array_push($option,$opt);
            }
            //halt($opt);
            //返回数据
            return $option;
        }else{
            //若没有班级 则返回 暂无数据
            return "<option value=''>暂无数据</option>";
        }
    }

    public function edit($id)
    {
        //获取当前学生的id
        $student = StudentModel::get($id);
        //查询出学校所有记录
        $school=SchoolModel::all();
        //查询班级表中的学校id值
        $schoolId=classModel::where('id',$student->class_id)->value('school_id');
        //halt($schId);
        //查询出班级表的school_id
        $class=classModel::where('school_id',$schoolId)->select();
        //映射页面，接收返回的值
        return view('',
            ['student'=>$student,'school'=>$school,'schoolId'=>$schoolId,'class'=>$class]);
    }

    public function editPost()
    {
        //接收edit页面传的数据
        $data=input();
        //从数据库获取这条数据的id，并更新数据
        $edit=StudentModel::update($data,['id'=>$data['id']]);
        //是否成功
        if ($edit) {
            //成功
            $this->success("修改成功", url("student/index"));
        } else {
            //失败
            $this->error("修改失败", url("student/edit"));
        }
    }

    /**
     * 删除指定资源
     *
     * @param  int  $id
     * @return \think\Response
     */
    public function delete($id)
    {

        //获取要删除的数据的id
        $data=input();
        //删除在数据库学生表获取的id的数据
        $del=StudentModel::destroy($data['id']);
        //判断是否成功
        if ($del){
            //成功
            $this->success("删除成功", url("student/index"));
        }else{
            //失败
            $this->error("删除失败", url("student/index"));
        }

    }
public function deletes(){

}

    public function exportExcel()
    {
        // 查询要导出的数据
        $data = Db::name('student')
            //连接班级表 班级id=学生表的班级id
            ->join('cmf_class', 'cmf_student.class_id=cmf_class.id', 'LEFT')
            //连接学校表 学校id=班级表的学校id
            ->join('cmf_school', 'cmf_school.id=cmf_class.school_id', 'LEFT')
            //查询学生表所有数据
            ->field('cmf_student.*')
            //重命名 班级名为class_name
            ->field('cmf_class.name as class_name')
            //重命名 学校名为school_name
            ->field('cmf_school.name as school_name')
            ->select();
        // 实例化
        $spreadsheet = new Spreadsheet();
        // 获取活动单元格
        $sheet = $spreadsheet->getActiveSheet();

        // 获取单元格
        $cellA = $sheet->getCell('A1');
        // 设置单元格的值
        $cellA->setValue('ID');
        // 设置 A 列 列宽
        $sheet->getColumnDimension('A')->setWidth(10);
        // 设置第一行 行高
        $sheet->getRowDimension(1)->setRowHeight(20);

        $cellB = $sheet->getCell('B1');
        $cellB->setValue('学生头像');
        $sheet->getColumnDimension('B')->setWidth(10);

        $cellC = $sheet->getCell('C1');
        $cellC->setValue('学生姓名');
        $sheet->getColumnDimension('C')->setWidth(10);

        $cellD = $sheet->getCell('D1');
        $cellD->setValue('学生身份证');
        $sheet->getColumnDimension('D')->setWidth(16);

        $cellD = $sheet->getCell('E1');
        $cellD->setValue('学生住址');
        $sheet->getColumnDimension('E')->setWidth(20);

        $cellE = $sheet->getCell('F1');
        $cellE->setValue('学生性别');
        $sheet->getColumnDimension('F')->setWidth(10);

        $cellF = $sheet->getCell('G1');
        $cellF->setValue('所属班级');
        $sheet->getColumnDimension('G')->setWidth(20);

        $cellG = $sheet->getCell('H1');
        $cellG->setValue('所属学校');
        $sheet->getColumnDimension('H')->setWidth(40);

        $cellH = $sheet->getCell('I1');
        $cellH->setValue('创建时间');
        $sheet->getColumnDimension('I')->setWidth(30);

        // 设置样式 标题
        $styleArray = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '黑体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 设置样式 正文
        $styleArrayBody = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '宋体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 应用样式
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        $sheet->getStyle('B1')->applyFromArray($styleArray);
        $sheet->getStyle('C1')->applyFromArray($styleArray);
        $sheet->getStyle('D1')->applyFromArray($styleArray);
        $sheet->getStyle('E1')->applyFromArray($styleArray);
        $sheet->getStyle('F1')->applyFromArray($styleArray);
        $sheet->getStyle('G1')->applyFromArray($styleArray);
        $sheet->getStyle('H1')->applyFromArray($styleArray);
        $sheet->getStyle('I1')->applyFromArray($styleArray);


        // 从 A2 开始填充数据
        foreach ($data as $k => $v) {
            $n = $k + 2;
            // 获取单元格
            $cellA = $sheet->getCell('A'.$n);
            // 设置单元格的值
            $cellA->setValue($v['id']);

            $cellB = $sheet->getCell('B'.$n);
            $cellB->setValue('');

            $cellC = $sheet->getCell('C'.$n);
            $cellC->setValue($v['name']);

            $cellD = $sheet->getCell('D'.$n);
            $cellD->setValue($v['idcard']);

            $cellD = $sheet->getCell('E'.$n);
            $cellD->setValue($v['address']);

            $cellE = $sheet->getCell('F'.$n);
            $cellE->setValue($v['sex']);

            $cellF = $sheet->getCell('G'.$n);
            $cellF->setValue($v['class_name']);

            $cellG = $sheet->getCell('H'.$n);
            $cellG->setValue($v['school_name']);

            $cellH = $sheet->getCell('I'.$n);
            $cellH->setValue($v['create_time']);

            // 再给表格体设置样式
            $sheet->getStyle('A'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('B'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('C'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('D'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('E'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('F'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('G'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('H'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('I'.$n)->applyFromArray($styleArrayBody);

        }

        $file_name = '学生列表.xlsx';
        // 实例化导出类
        header('Content-Type:application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename='.$file_name);
        header('Cache-Control:max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

    public function importExcel()
    {
        // 接收文件
        $file = $_FILES['file']['tmp_name'];
        // 创建读操作
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        // 打开文件、载入excel表格
        $spreadsheet = $reader->load($file);
        // 获取活动工作薄
        $sheet = $spreadsheet->getActiveSheet();

        // 获取总列数
        //$highestColumn = $sheet->getHighestColumn();
        // 获取总行数
        $highestRow = $sheet->getHighestRow();
        // 将列转为数字
        //$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

        // 存放插入成功的记录
        $successLog = [];
        // 存放插入失败的记录
        $failLog = [];

        // 从第二行开始读取表格数据，循环写入数据库
        for ($i = 2; $i <= $highestRow; $i++) {
            // 第 1 次循环获取第 3 列 第 2 行单元格的值，第 2 次循环获取第 3 列 第 3 行单元格的值
            $name = $sheet->getCellByColumnAndRow(3,$i)->getValue();
            $idcard = $sheet->getCellByColumnAndRow(4,$i)->getValue();
            $address = $sheet->getCellByColumnAndRow(5,$i)->getValue();
            $sex = $sheet->getCellByColumnAndRow(6,$i)->getValue();
            $class = $sheet->getCellByColumnAndRow(7,$i)->getValue();
            $data = [
                'name' => $name,
                'idcard' => $idcard,
                'address' => $address,
                'sex' => $sex,
                'class_id' => $class,
                'create_time' => date('Y-m-d H:i:s', time())
            ];
            $insert = Db::name('student')->insert($data);
            if ($insert) {
                $successLog[] = '第'.$i.'条，插入成功';
            } else {
                $failLog[] = '第'.$i.'条，插入失败';
            }
        }

        // 将成功数和失败数返回给 ajax
        return json(['success' => count($successLog), 'fail' => count($failLog)]);
    }
}